package com.alphacsp.theedge2012.data.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import com.alphacsp.theedge2012.data.entities.Event;
import com.alphacsp.theedge2012.data.entities.Session;
import com.alphacsp.theedge2012.data.entities.Speaker;
import org.intellij.lang.annotations.Language;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: Gilad
 * Date: 29/10/10
 * Time: 23:49
 */
public class DatabaseHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "TheEdge";

    public static final String SPEAKERS_TABLE = "speakers";
    public static final String SESSIONS_TABLE = "sessions";
    public static final String SCHEDULE_TABLE = "schedule";
    public static final String DATES_TABLE = "dates";
    public static final String FILE_TABLE = "files";


    @Language("SQLite")
    static final String CREATE_SPEAKERS_TABLE = "CREATE VIRTUAL TABLE " + SPEAKERS_TABLE + " USING fts3 (speaker_name  TEXT , bio TEXT, company TEXT, image_uri TEXT)";
    @Language("SQLite")
    static final String CREATE_SESSIONS_TABLE = "CREATE VIRTUAL TABLE " + SESSIONS_TABLE + " USING fts3  (topic  TEXT , presenter TEXT, abstract TEXT)";
    @Language("SQLite")
    static final String CREATE_SCHEDULE_TABLE = "CREATE VIRTUAL TABLE " + SCHEDULE_TABLE + " USING fts3  (event_name  TEXT , start_time TEXT, end_time TEXT, track INTEGER , track_color INTEGER , presenter TEXT)";
    @Language("SQLite")
    static final String CREATE_MODIFICATION_DATES_TABLE = "CREATE  TABLE " + DATES_TABLE + " (service  TEXT , modified_date INTEGER  )";
    @Language("SQLite")
    static final String CREATE_FILES_URI_TABLE = "CREATE TABLE " + FILE_TABLE + " (image_uri  TEXT , file_path INTEGER  )";


    public DatabaseHelper(Context context) {
        this(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        Log.i(this.getClass().getSimpleName(), "Creating DB");
        sqLiteDatabase.execSQL(CREATE_SPEAKERS_TABLE);
        sqLiteDatabase.execSQL(CREATE_SESSIONS_TABLE);
        sqLiteDatabase.execSQL(CREATE_SCHEDULE_TABLE);
        sqLiteDatabase.execSQL(CREATE_MODIFICATION_DATES_TABLE);
        sqLiteDatabase.execSQL(CREATE_FILES_URI_TABLE);

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL("DROP TABLE " + SPEAKERS_TABLE);
        sqLiteDatabase.execSQL("DROP TABLE " + SESSIONS_TABLE);
        sqLiteDatabase.execSQL("DROP TABLE " + SCHEDULE_TABLE);
        sqLiteDatabase.execSQL("DROP TABLE " + DATES_TABLE);
        sqLiteDatabase.execSQL("DROP TABLE " + FILE_TABLE);
        onCreate(sqLiteDatabase);
    }

    public List<Speaker> getAllSpeakers() {
        final List<Speaker> speakerList = new ArrayList<Speaker>();
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("select * from " + SPEAKERS_TABLE, null);
        if (cursor.moveToFirst()) {
            do {
                final Speaker speaker = new Speaker();
                speaker.setName(cursor.getString(cursor.getColumnIndex("speaker_name")));
                speaker.setBio(cursor.getString(cursor.getColumnIndex("bio")));
                speaker.setCompany(cursor.getString(cursor.getColumnIndex("company")));
                speaker.setImageUrl(cursor.getString(cursor.getColumnIndex("image_uri")));
                speakerList.add(speaker);
            } while (cursor.moveToNext());
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return speakerList;
    }

    public List<Session> getAllSessions() {
        final List<Session> sessionList = new ArrayList<Session>();
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("select * from " + SESSIONS_TABLE, null);
        if (cursor.moveToFirst()) {
            do {
                final Session session = new Session();
                session.setTopic(cursor.getString(cursor.getColumnIndex("topic")));
                session.setPresenter(cursor.getString(cursor.getColumnIndex("presenter")));
                session.setSessionAbstract(cursor.getString(cursor.getColumnIndex("abstract")));
                sessionList.add(session);
            } while (cursor.moveToNext());
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return sessionList;
    }

    public List<Event> getAllEvents() {
        final List<Event> eventList = new ArrayList<Event>();
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("select * from " + SCHEDULE_TABLE, null);
        if (cursor.moveToFirst()) {
            do {
                final Event event = new Event();
                event.setName(cursor.getString(cursor.getColumnIndex("event_name")));
                event.setStartTime(cursor.getString(cursor.getColumnIndex("start_time")));
                event.setEndTime(cursor.getString(cursor.getColumnIndex("end_time")));
                event.setTrack(cursor.getInt(cursor.getColumnIndex("track")));
                event.setTrackColor(cursor.getInt(cursor.getColumnIndex("track_color")));
                event.setPresenter(cursor.getString(cursor.getColumnIndex("presenter")));
                eventList.add(event);
            } while (cursor.moveToNext());
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return eventList;
    }

    public Session getSessionByName(String sessionName) {
        sessionName = sessionName.replaceAll("-", " ");
        Session session = null;
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("select * from " + SESSIONS_TABLE + " where topic match'" + sessionName + "'", null);
        if (cursor.moveToFirst()) {
            session = new Session();
            session.setTopic(cursor.getString(cursor.getColumnIndex("topic")));
            session.setPresenter(cursor.getString(cursor.getColumnIndex("presenter")));
            session.setSessionAbstract(cursor.getString(cursor.getColumnIndex("abstract")));
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return session;
    }

    public List<Session> getSessionsBySpeaker(String speakerName) {
        final List<Session> sessionList = new ArrayList<Session>();
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("select * from " + SESSIONS_TABLE + " where presenter match'" + speakerName + "'", null);
        if (cursor.moveToFirst()) {
            do {
                final Session session = new Session();
                session.setTopic(cursor.getString(cursor.getColumnIndex("topic")));
                session.setPresenter(cursor.getString(cursor.getColumnIndex("presenter")));
                session.setSessionAbstract(cursor.getString(cursor.getColumnIndex("abstract")));
                sessionList.add(session);
            } while (cursor.moveToNext());
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return sessionList;
    }

    public List<Session> getParallelSessions(String sessionName) {
        final List<Session> sessionList = new ArrayList<Session>();
        String startTime = null;
        int track;
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        Cursor cursor = readableDatabase.rawQuery("select * from " + SCHEDULE_TABLE + " where event_name match '" + sessionName.replaceAll("-", " ") + "'", null);
        if (cursor.moveToFirst()) {
            startTime = cursor.getString(cursor.getColumnIndex("start_time"));
            track = cursor.getInt(cursor.getColumnIndex("track"));
        } else {
            return sessionList;
        }
        if (track == 0) {
            return sessionList;
        }
        final List<String> sessionsName = new ArrayList<String>();
        final String[] split = startTime.split(":");
        cursor = readableDatabase.rawQuery("select * from " + SCHEDULE_TABLE + " where start_time match '" + split[0] + " NEAR/0 " + split[1] + "'", null);
        if (cursor.moveToFirst()) {
            do {
                sessionsName.add((cursor.getString(cursor.getColumnIndex("event_name"))));
            } while (cursor.moveToNext());
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        for (String name : sessionsName) {
            if (!sessionName.equals(name)) {
                final Session sessionByName = getSessionByName(name);
                if (sessionByName != null) {
                    sessionList.add(sessionByName);
                }
            }
        }
        return sessionList;
    }

    public Date getLastModifiedDate(String table) {
        Date date = null;
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("select modified_date from " + DATES_TABLE + " where service='" + table + "'", null);
        if (cursor.moveToFirst()) {
            final long modifiedDate = cursor.getLong(cursor.getColumnIndex("modified_date"));
            date = new Date(modifiedDate);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return date;
    }

    public String getImageFilePath(String imageUri) {
        String filePath = null;
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("select file_path from " + FILE_TABLE + " where image_uri='" + imageUri + "'", null);
        if (cursor.moveToFirst()) {
            filePath = cursor.getString(cursor.getColumnIndex("file_path"));
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return filePath;
    }

    public void storeSpeakers(List<Speaker> speakerList, Date lastModifiedDate) {
        final SQLiteDatabase writableDatabase = getWritableDatabase();
        final ContentValues contentValues = new ContentValues();
        writableDatabase.beginTransaction();
        writableDatabase.delete(SPEAKERS_TABLE, null, null);
        for (Speaker speaker : speakerList) {
            contentValues.put("speaker_name", speaker.getName());
            contentValues.put("bio", speaker.getBio());
            contentValues.put("company", speaker.getCompany());
            contentValues.put("image_uri", speaker.getImageUrl());
            writableDatabase.insertOrThrow(SPEAKERS_TABLE, null, contentValues);
            contentValues.clear();
        }
        storeModificationDates(SPEAKERS_TABLE, lastModifiedDate);
        writableDatabase.setTransactionSuccessful();
        writableDatabase.endTransaction();
    }

    public void storeSessions(List<Session> sessionList, Date lastModifiedDate) {
        final SQLiteDatabase writableDatabase = getWritableDatabase();
        final ContentValues contentValues = new ContentValues();
        writableDatabase.beginTransaction();
        writableDatabase.delete(SESSIONS_TABLE, null, null);
        for (Session session : sessionList) {
            contentValues.put("topic", session.getTopic());
            contentValues.put("presenter", session.getPresenter());
            contentValues.put("abstract", session.getSessionAbstract());
            writableDatabase.insertOrThrow(SESSIONS_TABLE, null, contentValues);
            contentValues.clear();
        }
        storeModificationDates(SESSIONS_TABLE, lastModifiedDate);
        writableDatabase.setTransactionSuccessful();
        writableDatabase.endTransaction();

    }

    public void storeSchedule(List<Event> eventList, Date lastModifiedDate) {
        final SQLiteDatabase writableDatabase = getWritableDatabase();
        final ContentValues contentValues = new ContentValues();
        writableDatabase.beginTransaction();
        writableDatabase.delete(SCHEDULE_TABLE, null, null);
        for (Event event : eventList) {
            contentValues.put("event_name", event.getName());
            contentValues.put("start_time", event.getStartTime());
            contentValues.put("end_time", event.getEndTime());
            contentValues.put("track", event.getTrack());
            contentValues.put("track_color", event.getTrackColor());
            contentValues.put("presenter", event.getPresenter());
            writableDatabase.insertOrThrow(SCHEDULE_TABLE, null, contentValues);
            contentValues.clear();
        }
        storeModificationDates(SCHEDULE_TABLE, lastModifiedDate);
        writableDatabase.setTransactionSuccessful();
        writableDatabase.endTransaction();

    }

    public List<Session> searchSessions(String query) {
        final List<Session> sessionList = new ArrayList<Session>();
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("select * from " + SESSIONS_TABLE + " where " + SESSIONS_TABLE + " match '" + query + "'", null);
        if (cursor.moveToFirst()) {
            do {
                final Session session = new Session();
                session.setTopic(cursor.getString(cursor.getColumnIndex("topic")));
                session.setPresenter(cursor.getString(cursor.getColumnIndex("presenter")));
                session.setSessionAbstract(cursor.getString(cursor.getColumnIndex("abstract")));
                sessionList.add(session);
            } while (cursor.moveToNext());
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return sessionList;
    }

    public List<Speaker> searchSpeakers(String query) {
        final List<Speaker> speakerList = new ArrayList<Speaker>();
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("select * from " + SPEAKERS_TABLE + " where " + SPEAKERS_TABLE + " match '" + query + "'", null);
        if (cursor.moveToFirst()) {
            do {
                final Speaker speaker = new Speaker();
                speaker.setName(cursor.getString(cursor.getColumnIndex("speaker_name")));
                speaker.setBio(cursor.getString(cursor.getColumnIndex("bio")));
                speaker.setCompany(cursor.getString(cursor.getColumnIndex("company")));
                speaker.setImageUrl(cursor.getString(cursor.getColumnIndex("image_uri")));
                speakerList.add(speaker);
            } while (cursor.moveToNext());
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return speakerList;
    }

    private void storeModificationDates(String tableName, Date date) {
        final SQLiteDatabase writableDatabase = getWritableDatabase();
        final ContentValues contentValues = new ContentValues();
        writableDatabase.beginTransaction();
        contentValues.put("service", tableName);
        contentValues.put("modified_date", date.getTime());
        writableDatabase.replace(DATES_TABLE, null, contentValues);
        contentValues.clear();
        writableDatabase.setTransactionSuccessful();
        writableDatabase.endTransaction();
    }

    public void storeFileLocation(String imageUri, String absolutePath) {
        final SQLiteDatabase writableDatabase = getWritableDatabase();
        final ContentValues contentValues = new ContentValues();
        writableDatabase.beginTransaction();
        contentValues.put("image_uri", imageUri);
        contentValues.put("file_path", absolutePath);
        writableDatabase.insert(FILE_TABLE, null, contentValues);
        contentValues.clear();
        writableDatabase.setTransactionSuccessful();
        writableDatabase.endTransaction();
    }

    public void closeDatabase() {
        if (getWritableDatabase().isOpen()) {
            getWritableDatabase().close();
        }
        if (getReadableDatabase().isOpen()) {
            getReadableDatabase().close();
        }
    }
}
